﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Office.Interop;
using Excel = Microsoft.Office.Interop.Excel;
using Context;
using System.ComponentModel;


namespace LP
{
    public class DataSource : INotifyPropertyChanged
    {

        public event PropertyChangedEventHandler PropertyChanged;
        protected void OnPropertyChanged(string name)
        {
            PropertyChangedEventHandler handler = PropertyChanged;
            if (handler != null)
            {
                handler(this, new PropertyChangedEventArgs(name));
            }
        }

        // Propriedade (Binding) para o ano selecionado da ComboBox (Desde o ano:)
        private int selectedStartYear;
        public void setSelectedStartYear(int year)
        {
            this.selectedStartYear = year;
        }

        // Propriedade (Binding) para o ano selecionado da ComboBox (Até ao ano:)
        private int selectedFinalYear;
        public void setSelectedFinalYear(int year)
        {
            this.selectedFinalYear = year;
        }
        
        // Propriedade (Binding) para a lista de dados da ListBox (Dados)
        public ObservableCollection<BoolStringClass> DataList { get; set; }

        // Propriedade (Binding) para a lista de tipo de gráfico da ComboBox (Tipo de gráfico)
        public ObservableCollection<String> GraphTypeList { get; set; }

        // Propriedade (Binding) p
        public ObservableCollection<int> FromYearList { get; set; }
        public int FromYear { get; set; }
        public int ToYear { get; set; }
        
        // Propriedade (Binding) para a lista de items da ComboBox (Até ao ano:)
        private ObservableCollection<int> toYearList;
        public ObservableCollection<int> ToYearList
        {
            get { return this.toYearList; }
            set
            {
                this.toYearList = value;
                OnPropertyChanged("ToYearList");
            }
        }

        // Propriedade (Binding) para o index selecionado da ComboBox (Até ao ano:)
        private int selectedIndex;
        public int SelectedIndex
        {
            get { return this.selectedIndex; }
            set
            {
                this.selectedIndex = value;
                OnPropertyChanged("SelectedIndex");
            }
        }

        // Caminho do ficheiro Excel e Database
        private String databaseConnection;
        private String excelConnection;

        //Nomes dos dados
        private String[] dataNames = {
                                         "IPC Global (Índice)",
                                         "Variação Anual (Taxa de Inflação) (%)",
                                         "Remuneração mínima mensal (€)",
                                         "Remuneração máxima mensal (€)",
                                         "PIB per capita anual (€)",
                                         "Rendimento nacional bruto per capita anual (€)",
                                         "Rendimento disponível bruto per capita anual (€)",
                                         "Remunerações per capita anual (€)"
                                     };

        // Contrutor da classe DataSource
        /// <summary>
        /// Constructor of DataSource class
        /// </summary>
        public DataSource()
        {
            String executablePath = AppDomain.CurrentDomain.BaseDirectory;

            // Configurations
            // Custom path:
            // String databaseConnection = @"Data Source=C:\Users\Miguel\Desktop\database3.db;FailIfMissing=False";
            // Path to debug folder (database):
            this.databaseConnection = @"Data Source=" + executablePath + "database.db;FailIfMissing=False";

            // Path to debug folder (excel):
            this.excelConnection = executablePath + "IPC_Portugal_1977_2013.xls";

            // Read and write data to database
            this.readExcelAndWriteDatabase();

            // Tipo de dados - Preenchimento da ComboBox
            GraphTypeList = new ObservableCollection<String>();
            GraphTypeList.Add("Linhas");
            GraphTypeList.Add("Barras");
            GraphTypeList.Add("Dispersão");

            // Dados - Preenchimento da ListBox
            DataList = new ObservableCollection<BoolStringClass>();
            DataList.Add(new BoolStringClass { TheText = this.dataNames[0], IsSelected = false });
            DataList.Add(new BoolStringClass { TheText = this.dataNames[1], IsSelected = false });
            DataList.Add(new BoolStringClass { TheText = this.dataNames[2], IsSelected = false });
            DataList.Add(new BoolStringClass { TheText = this.dataNames[3], IsSelected = false });
            DataList.Add(new BoolStringClass { TheText = this.dataNames[4], IsSelected = false });
            DataList.Add(new BoolStringClass { TheText = this.dataNames[5], IsSelected = false });
            DataList.Add(new BoolStringClass { TheText = this.dataNames[6], IsSelected = false });
            DataList.Add(new BoolStringClass { TheText = this.dataNames[7], IsSelected = false });
            // Desde o ano - Preenchimento da ComboBox
            FromYearList = this.getStartYears();

            // Definir os anos
            this.FromYear = this.FromYearList.ElementAt(0);
            this.ToYear = this.FromYearList.ElementAt(this.FromYearList.Count - 1);
        }

        // Ler folha de Excel
        /// <summary>
        /// Read data from Excel and write it on SQLITE database
        /// </summary>
        private void readExcelAndWriteDatabase()
        {
            // Limpar DB
            Console.Out.WriteLine("Database: " + this.databaseConnection);
            DataContext context = new DataContext(this.databaseConnection);

            if (context.DatabaseExists())
            {
                Console.Out.WriteLine("Deleting old database..");
                context.DeleteDatabase();
            }
            Console.Out.WriteLine("Creating database..");
            context.CreateDatabase();

            // Definir localização da EXCEL

            Console.Out.WriteLine("Excel: " + this.excelConnection);

            // Obter o Excel e abrir
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(this.excelConnection, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            Excel._Worksheet xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1];
            Excel.Range xlRange = xlWorksheet.UsedRange;

            // Obter o número de linhas e colunas realmente ocupadas
            int iTotalColumns = xlRange.Columns.Count;
            int iTotalRows = xlRange.Rows.Count;
            xlWorksheet.Columns.ClearFormats();
            xlWorksheet.Rows.ClearFormats();
            iTotalColumns = xlRange.Columns.Count;
            iTotalRows = xlRange.Rows.Count;

            double[] values = new double[iTotalColumns];
            int valuesIndex = 0;
            int offset = 2;

            // offset-1 porque ignoramos a 1a linha
            // Leitura de linhas
            for (int i = offset; i < iTotalRows + offset - 1; i++)
            {
                Console.Out.WriteLine("Reading data of the year: " + xlWorksheet.Cells[i, offset].Value);
                // Leitra de colunas
                for (int j = offset; j < iTotalColumns + offset; j++)
                {

                    // Se a celula estiver vazia colocar valor a 0
                    if (xlWorksheet.Cells[i, j].Value == null)
                    {
                        values[valuesIndex++] = 0;
                    }
                    else
                    {
                        values[valuesIndex++] = xlWorksheet.Cells[i, j].Value;
                    }

                }
                writeValuesToDatabase(context, values);
                valuesIndex = 0;
            }

            // Terminar processo do Excel - Rever
            xlWorkbook.Close(0);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
            xlApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
        }

        // Inserir dados na DB
        /// <summary>
        /// Write values on database
        /// </summary>
        /// <param name="context">DataContext connection</param>
        /// <param name="values">Values to insert</param>
        private void writeValuesToDatabase(DataContext context, double[] values)
        {
            // Create object
            Dados dados = new Dados();
            dados.ano = Convert.ToInt32(values[0]);
            dados.ipc_global_1981_100 = values[1];
            dados.variacao_anual_taxa_de_inflacao = values[2];
            dados.remuneracao_minima_mensal = values[3];
            dados.remuneracao_maxima_mensal = values[4];
            dados.pib_per_capita_anual = values[5];
            dados.rendimento_nacional_bruto_per_capita_anual = values[6];
            dados.rendimento_disponivel_bruto_per_capita_anual = values[7];
            dados.remuneracoes_per_capita_anual = values[8];

            // Insert row in database

            context.Dados.InsertOnSubmit(dados);

            // Send the changes to the database.
            // Until you do it, the changes are cached on the client side.
            context.SubmitChanges();
        }

        // Classe para usar na ListBox
        /// <summary>
        /// Class used on ListBox (String and Boolean values)
        /// </summary>
        public class BoolStringClass
        {
            public string TheText { get; set; }
            public bool IsSelected { get; set; }
        }

        // Obter a lista dos anos iniciais "Desde o ano" pela DB
        /// <summary>
        /// Gets a list of all available years values on database
        /// </summary>
        /// <returns>Returns a list of values to be used on ComboBox</returns>
        public ObservableCollection<int> getStartYears()
        {
            ObservableCollection<int> years = new ObservableCollection<int>();
            DataContext context = new DataContext(this.databaseConnection);
            var query = from it in context.Dados
                        orderby it.ano
                        select it;

            foreach (Dados test in query)
                years.Add(test.ano);
            return years;
        }

        // Obter a lista dos anos finais "Ate ao ano" pela DB
        /// <summary>
        /// Gets a list of available values from database to the ComboBox "Até ao ano:"
        /// </summary>
        /// <returns>Returns a list of values to be used on ComboBox</returns>
        public ObservableCollection<int> getFinalYears()
        {
            ObservableCollection<int> years = new ObservableCollection<int>();
            DataContext context = new DataContext(this.databaseConnection);
            var query = from it in context.Dados
                        orderby it.ano
                        select it;
            foreach (Dados test in query)
            {
                if (test.ano >= this.FromYear)
                    years.Add(test.ano);
            }
            return years;
        }

        // Atualizar a lista da combobox "Ate ao ano" para eliminar anos anteriores ao ano inicial
        /// <summary>
        /// Updates the ComboBox with "Até ao ano:" values, deleting previous values of "From year:" selected value
        /// </summary>
        public void updateFinalYears()
        {
            this.ToYearList = this.getFinalYears();
            if(this.FromYear > this.ToYear)
                this.SelectedIndex = 0;
        }

        // Obter uma lista com os nomes dos dados selecionados
        /// <summary>
        /// Gets the current selected data types on ListBox
        /// </summary>
        /// <returns>Returns a list with data type names (strings).</returns>
        public List<string> getSelectedDataNames()
        {
            List<string> data = new List<string>();

            foreach (BoolStringClass info in this.DataList)
            {
                if (info.IsSelected)
                    data.Add(info.TheText);
            }
            return data;
        }

        // Obter os dados que estão na base de dados para poder criar um gráfico
        /// <summary>
        /// Gets data saved on database (based on current selected years and selected data types)
        /// </summary>
        /// <returns>Returns a list of lists with values (doubles)</returns>
        public List<List<double>> getData()
        {
            Console.Out.WriteLine("From: " + this.FromYear + "To: " + this.ToYear);
            List<List<double>> data = new List<List<double>>();
            DataContext context = new DataContext(this.databaseConnection);
            var query = from it in context.Dados
                        where ((it.ano >= this.FromYear) && (it.ano <= this.ToYear))
                        orderby it.ano
                        select it;

            foreach (String dataName in this.getSelectedDataNames())
            {
                List<double> currentData = new List<double>();
                foreach (Dados value in query)
                {
                    if (dataName.Equals(this.dataNames[0]))
                        currentData.Add(value.ipc_global_1981_100);
                    if (dataName.Equals(this.dataNames[1]))
                        currentData.Add(value.variacao_anual_taxa_de_inflacao);
                    if (dataName.Equals(this.dataNames[2]))
                        currentData.Add(value.remuneracao_minima_mensal);
                    if (dataName.Equals(this.dataNames[3]))
                        currentData.Add(value.remuneracao_maxima_mensal);
                    if (dataName.Equals(this.dataNames[4]))
                        currentData.Add(value.pib_per_capita_anual);
                    if (dataName.Equals(this.dataNames[5]))
                        currentData.Add(value.rendimento_nacional_bruto_per_capita_anual);
                    if (dataName.Equals(this.dataNames[6]))
                        currentData.Add(value.rendimento_disponivel_bruto_per_capita_anual);
                    if (dataName.Equals(this.dataNames[7]))
                        currentData.Add(value.remuneracoes_per_capita_anual);
                }
                data.Add(currentData);
            }

            return data;
        }
    }

}
